#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pymysql
from config import Config

def clean_mysql_tables():
    """清理MySQL表和索引"""
    try:
        conn = pymysql.connect(
            host=Config.DB_HOST,
            port=Config.DB_PORT,
            user=Config.DB_USER,
            password=Config.DB_PASSWORD,
            database=Config.DB_NAME
        )
        
        cursor = conn.cursor()
        
        print("🧹 清理MySQL表和索引...")
        
        # 删除所有相关表
        tables_to_drop = ['sleep_monitoring_records', 'room']
        
        for table in tables_to_drop:
            try:
                cursor.execute(f"DROP TABLE IF EXISTS {table}")
                print(f"✅ 表 {table} 已删除")
            except Exception as e:
                print(f"⚠️  删除表 {table} 时出错: {e}")
        
        conn.commit()
        conn.close()
        
        print("✅ MySQL表清理完成")
        return True
        
    except Exception as e:
        print(f"❌ 清理MySQL表失败: {e}")
        return False

def recreate_tables_with_peewee():
    """使用Peewee重新创建表"""
    try:
        print("\n🔄 使用Peewee重新创建表...")
        
        # 重置数据库连接
        from config import reset_database
        reset_database()
        
        from models.sleep_monitoring import SleepMonitoringRecord, Room
        from config import get_database
        
        db = get_database()
        db.connect()
        
        # 创建表
        Room.create_table(safe=True)
        print("✅ Room表已创建")
        
        SleepMonitoringRecord.create_table(safe=True)
        print("✅ SleepMonitoringRecord表已创建")
        
        db.close()
        
        return True
        
    except Exception as e:
        print(f"❌ 重新创建表失败: {e}")
        return False

def verify_tables():
    """验证表结构"""
    try:
        conn = pymysql.connect(
            host=Config.DB_HOST,
            port=Config.DB_PORT,
            user=Config.DB_USER,
            password=Config.DB_PASSWORD,
            database=Config.DB_NAME
        )
        
        cursor = conn.cursor()
        
        print("\n🔍 验证表结构...")
        
        # 检查表是否存在
        cursor.execute("SHOW TABLES")
        tables = [row[0] for row in cursor.fetchall()]
        print(f"✅ 数据库中的表: {tables}")
        
        # 检查sleep_monitoring_records表结构
        if 'sleep_monitoring_records' in tables:
            cursor.execute('DESCRIBE sleep_monitoring_records')
            columns = cursor.fetchall()
            print(f"✅ sleep_monitoring_records表有 {len(columns)} 个字段")
            
            # 检查关键字段
            column_names = [col[0] for col in columns]
            required_fields = ['data_raw_channel_1', 'data_signal_quality', 'data_battery_level']
            
            missing_fields = [field for field in required_fields if field not in column_names]
            if missing_fields:
                print(f"❌ 缺失字段: {missing_fields}")
            else:
                print("✅ 所有必需字段都存在")
        
        conn.close()
        return True
        
    except Exception as e:
        print(f"❌ 验证表结构失败: {e}")
        return False

def main():
    """主函数"""
    print("🚀 修复MySQL表结构...")
    
    # 1. 清理现有表
    if clean_mysql_tables():
        print("\n✅ 第1步: 表清理完成")
    else:
        print("\n❌ 第1步: 表清理失败")
        return
    
    # 2. 重新创建表
    if recreate_tables_with_peewee():
        print("\n✅ 第2步: 表重新创建完成")
    else:
        print("\n❌ 第2步: 表重新创建失败")
        return
    
    # 3. 验证表结构
    if verify_tables():
        print("\n✅ 第3步: 表结构验证完成")
        print("\n🎉 MySQL表结构修复成功！现在可以重新运行数据模拟了。")
    else:
        print("\n❌ 第3步: 表结构验证失败")

if __name__ == '__main__':
    main()